import pymssql
from datetime import datetime  # 引入时间，python 中的date是字符串
import sys
sys.stdout.reconfigure(encoding='utf-8')

###连接函数
def get_database_connection():
    try:
        # 尝试建立数据库连接
        conn = pymssql.connect(server='127.0.0.1', user='sa', password='123456', database='system')
        # 如果连接成功，打印成功消息
        print("数据库连接成功")
        return conn
    except pymssql.Error as e:
        # 如果连接失败，打印错误信息
        print("数据库连接失败：", e)
        return None

    ### 实体类定义
class PrimaryDisciplineInfo:
        def __init__(self, primary_discipline_id, discipline_name, discipline_level, discipline_description,
                     discipline_type):
            self.primary_discipline_id = primary_discipline_id
            self.discipline_name = discipline_name
            self.discipline_level = discipline_level
            self.discipline_description = discipline_description
            self.discipline_type = discipline_type

class SecondaryDisciplineInfo:
        def __init__(self, secondary_discipline_id, discipline_name, discipline_level, parent_discipline_id,
                     discipline_description, discipline_type):
            self.secondary_discipline_id = secondary_discipline_id
            self.discipline_name = discipline_name
            self.discipline_level = discipline_level
            self.parent_discipline_id = parent_discipline_id
            self.discipline_description = discipline_description
            self.discipline_type = discipline_type

class TutorInfo:
        def __init__(self, tutor_id, tutor_name, tutor_title, tutor_photo, tutor_introduction, tutor_email, tutor_phone,
                     tutor_discipline):
            self.tutor_id = tutor_id
            self.tutor_name = tutor_name
            self.tutor_title = tutor_title
            self.tutor_photo = tutor_photo
            self.tutor_introduction = tutor_introduction
            self.tutor_email = tutor_email
            self.tutor_phone = tutor_phone
            self.tutor_discipline = tutor_discipline

class AdmissionCatalog:
        def __init__(self, admission_catalog_id, discipline_id, discipline_direction_id, tutor_id, preliminary_subject,
                     reexamination_subject, total_quotas, supplementary_quotas, year, tutor_identity,
                     has_admission_qualification):
            self.admission_catalog_id = admission_catalog_id
            self.discipline_id = discipline_id
            self.discipline_direction_id = discipline_direction_id
            self.tutor_id = tutor_id
            self.preliminary_subject = preliminary_subject
            self.reexamination_subject = reexamination_subject
            self.total_quotas = total_quotas
            self.supplementary_quotas = supplementary_quotas
            self.year = year
            self.tutor_identity = tutor_identity
            self.has_admission_qualification = has_admission_qualification

class PrimaryDisciplineInfoDao:
                def __init__(self, db_connection):
                    self.db_connection = db_connection

                def get_primary_discipline_info(self, primary_discipline_id):
                    cursor = self.db_connection.cursor()
                    cursor.execute("SELECT * FROM Primary_Discipline_Info WHERE Primary_Discipline_ID = %s",
                                   (primary_discipline_id,))
                    result = cursor.fetchone()
                    cursor.close()
                    if result:
                        return PrimaryDisciplineInfo(*result)
                    return None

                def insert_primary_discipline_info(self, primary_discipline_info):
                    cursor = self.db_connection.cursor()
                    cursor.execute(
                        "INSERT INTO Primary_Discipline_Info (Primary_Discipline_ID, Discipline_Name, Discipline_Level, Discipline_Description, Discipline_Type) VALUES (%s, %s, %s, %s, %s)",
                        (primary_discipline_info.primary_discipline_id, primary_discipline_info.discipline_name,
                         primary_discipline_info.discipline_level, primary_discipline_info.discipline_description,
                         primary_discipline_info.discipline_type))
                    self.db_connection.commit()
                    cursor.close()

                def update_primary_discipline_info(self, primary_discipline_info):
                    cursor = self.db_connection.cursor()
                    cursor.execute(
                        "UPDATE Primary_Discipline_Info SET Discipline_Name = %s, Discipline_Level = %s, Discipline_Description = %s, Discipline_Type = %s WHERE Primary_Discipline_ID = %s",
                        (primary_discipline_info.discipline_name, primary_discipline_info.discipline_level,
                         primary_discipline_info.discipline_description, primary_discipline_info.discipline_type,
                         primary_discipline_info.primary_discipline_id))
                    self.db_connection.commit()
                    cursor.close()

                def delete_primary_discipline_info(self, primary_discipline_id):
                    cursor = self.db_connection.cursor()
                    cursor.execute("DELETE FROM Primary_Discipline_Info WHERE Primary_Discipline_ID = %s",
                                   (primary_discipline_id,))
                    self.db_connection.commit()
                    cursor.close()


class SecondaryDisciplineInfoDao:
    def __init__(self, db_connection):
        self.db_connection = db_connection

    def get_secondary_discipline_info(self, secondary_discipline_id):
        cursor = self.db_connection.cursor()
        cursor.execute("SELECT * FROM Secondary_Discipline_Info WHERE Secondary_Discipline_ID = %s",
                       (secondary_discipline_id,))
        result = cursor.fetchone()
        cursor.close()
        if result:
            return SecondaryDisciplineInfo(*result)
        return None

    def insert_secondary_discipline_info(self, secondary_discipline_info):
        cursor = self.db_connection.cursor()
        cursor.execute(
            "INSERT INTO Secondary_Discipline_Info (Secondary_Discipline_ID, Discipline_Name, Discipline_Level, Parent_Discipline_ID, Discipline_Description, Discipline_Type) VALUES (%s, %s, %s, %s, %s, %s)",
            (secondary_discipline_info.secondary_discipline_id, secondary_discipline_info.discipline_name,
             secondary_discipline_info.discipline_level, secondary_discipline_info.parent_discipline_id,
             secondary_discipline_info.discipline_description, secondary_discipline_info.discipline_type))
        self.db_connection.commit()
        cursor.close()

    def update_secondary_discipline_info(self, secondary_discipline_info):
        cursor = self.db_connection.cursor()
        cursor.execute(
            "UPDATE Secondary_Discipline_Info SET Discipline_Name = %s, Discipline_Level = %s, Parent_Discipline_ID = %s, Discipline_Description = %s, Discipline_Type = %s WHERE Secondary_Discipline_ID = %s",
            (secondary_discipline_info.discipline_name, secondary_discipline_info.discipline_level,
             secondary_discipline_info.parent_discipline_id, secondary_discipline_info.discipline_description,
             secondary_discipline_info.discipline_type, secondary_discipline_info.secondary_discipline_id))
        self.db_connection.commit()
        cursor.close()

    def delete_secondary_discipline_info(self, secondary_discipline_id):
        cursor = self.db_connection.cursor()
        cursor.execute("DELETE FROM Secondary_Discipline_Info WHERE Secondary_Discipline_ID = %s",
                       (secondary_discipline_id,))
        self.db_connection.commit()
        cursor.close()

class TutorInfoDao:
        def __init__(self, db_connection):
            self.db_connection = db_connection

        def get_tutor_info(self, tutor_id):
            cursor = self.db_connection.cursor()
            cursor.execute("SELECT * FROM Tutor_Info WHERE Tutor_ID = %s", (tutor_id,))
            result = cursor.fetchone()
            cursor.close()
            if result:
                return TutorInfo(*result)
            return None

        def insert_tutor_info(self, tutor_info):
            cursor = self.db_connection.cursor()
            cursor.execute(
                "INSERT INTO Tutor_Info (Tutor_ID, Tutor_Name, Tutor_Title, Tutor_Photo, Tutor_Introduction, Tutor_Email, Tutor_Phone, Tutor_Discipline) VALUES (%s, %s, %s, %s, %s, %s, %s, %s)",
                (tutor_info.tutor_id, tutor_info.tutor_name, tutor_info.tutor_title, tutor_info.tutor_photo,
                 tutor_info.tutor_introduction, tutor_info.tutor_email, tutor_info.tutor_phone,
                 tutor_info.tutor_discipline))
            self.db_connection.commit()
            cursor.close()

        def update_tutor_info(self, tutor_info):
            cursor = self.db_connection.cursor()
            cursor.execute(
                "UPDATE Tutor_Info SET Tutor_Name = %s, Tutor_Title = %s, Tutor_Photo = %s, Tutor_Introduction = %s, Tutor_Email = %s, Tutor_Phone = %s, Tutor_Discipline = %s WHERE Tutor_ID = %s",
                (tutor_info.tutor_name, tutor_info.tutor_title, tutor_info.tutor_photo, tutor_info.tutor_introduction,
                 tutor_info.tutor_email, tutor_info.tutor_phone, tutor_info.tutor_discipline, tutor_info.tutor_id))
            self.db_connection.commit()
            cursor.close()

        def delete_tutor_info(self, tutor_id):
            cursor = self.db_connection.cursor()
            cursor.execute("DELETE FROM Tutor_Info WHERE Tutor_ID = %s", (tutor_id,))
            self.db_connection.commit()
            cursor.close()


class AdmissionCatalogDao:
    def __init__(self, db_connection):
        self.db_connection = db_connection

    def get_admission_catalog(self, admission_catalog_id):
        cursor = self.db_connection.cursor()
        cursor.execute("SELECT * FROM Admission_Catalog WHERE Admission_Catalog_ID = %s", (admission_catalog_id,))
        result = cursor.fetchone()
        cursor.close()
        if result:
            return AdmissionCatalog(*result)
        return None

    def insert_admission_catalog(self, admission_catalog):
        cursor = self.db_connection.cursor()
        cursor.execute("INSERT INTO Admission_Catalog (Admission_Catalog_ID, Discipline_ID, Discipline_Direction_ID, Tutor_ID, Preliminary_Subject, Reexamination_Subject, Total_Quotas, Supplementary_Quotas, Year, Tutor_Identity, Has_Admission_Qualification) VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s)",
                       (admission_catalog.admission_catalog_id, admission_catalog.discipline_id, admission_catalog.discipline_direction_id, admission_catalog.tutor_id, admission_catalog.preliminary_subject, admission_catalog.reexamination_subject, admission_catalog.total_quotas, admission_catalog.supplementary_quotas, admission_catalog.year, admission_catalog.tutor_identity, admission_catalog.has_admission_qualification))
        self.db_connection.commit()
        cursor.close()

    def update_admission_catalog(self, admission_catalog):
        cursor = self.db_connection.cursor()
        cursor.execute("UPDATE Admission_Catalog SET Discipline_ID = %s, Discipline_Direction_ID = %s, Tutor_ID = %s, Preliminary_Subject = %s, Reexamination_Subject = %s, Total_Quotas = %s, Supplementary_Quotas = %s, Year = %s, Tutor_Identity = %s, Has_Admission_Qualification = %s WHERE Admission_Catalog_ID = %s",
                       (admission_catalog.discipline_id, admission_catalog.discipline_direction_id, admission_catalog.tutor_id, admission_catalog.preliminary_subject, admission_catalog.reexamination_subject, admission_catalog.total_quotas, admission_catalog.supplementary_quotas, admission_catalog.year, admission_catalog.tutor_identity, admission_catalog.has_admission_qualification, admission_catalog.admission_catalog_id))
        self.db_connection.commit()
        cursor.close()

    def delete_admission_catalog(self, admission_catalog_id):
        cursor = self.db_connection.cursor()
        cursor.execute("DELETE FROM Admission_Catalog WHERE Admission_Catalog_ID = %s", (admission_catalog_id,))
        self.db_connection.commit()
        cursor.close()

def test_database_operations():
    # 获取数据库连接
    db_connection = get_database_connection()
    if db_connection is None:
        print("数据库连接失败，无法进行测试")
        return

    # 创建持久层对象
    primary_discipline_dao = PrimaryDisciplineInfoDao(db_connection)
    secondary_discipline_dao = SecondaryDisciplineInfoDao(db_connection)
    tutor_info_dao = TutorInfoDao(db_connection)
    admission_catalog_dao = AdmissionCatalogDao(db_connection)

    # 测试 PrimaryDisciplineInfo
    print("Testing PrimaryDisciplineInfo...")
    primary_discipline_info = PrimaryDisciplineInfo(5, '新学科', '一级学科', '新学科领域', '学术型')
    primary_discipline_dao.insert_primary_discipline_info(primary_discipline_info)
    retrieved_info = primary_discipline_dao.get_primary_discipline_info(5)
    if retrieved_info:
        print(f"Retrieved: {retrieved_info.discipline_name}")
    primary_discipline_dao.delete_primary_discipline_info(5)

   # ## 测试 SecondaryDisciplineInfo
   #  print("Testing SecondaryDisciplineInfo...")
   #  secondary_discipline_info = SecondaryDisciplineInfo(5, '新二级学科', '二级学科', 1, '新二级学科领域', '学术型')
   #  secondary_discipline_dao.insert_secondary_discipline_info(secondary_discipline_info)
   #  retrieved_info = secondary_discipline_dao.get_secondary_discipline_info(5)
   #  if retrieved_info:
   #      print(f"Retrieved: {retrieved_info.discipline_name}")
   #  secondary_discipline_dao.delete_secondary_discipline_info(5)

    # 测试 TutorInfo
    print("Testing TutorInfo...")
    tutor_info = TutorInfo(5, '新导师', '教授', None, '新导师介绍', 'newtutor@example.com', '12345678901', 1)
    tutor_info_dao.insert_tutor_info(tutor_info)
    retrieved_info = tutor_info_dao.get_tutor_info(5)
    if retrieved_info:
        print(f"Retrieved: {retrieved_info.tutor_name}")
    tutor_info_dao.delete_tutor_info(5)

    # 测试 AdmissionCatalog
    print("Testing AdmissionCatalog...")
    admission_catalog_info = AdmissionCatalog(3, 1, 1, 1, '数学', '专业基础', 10, 2, 2024, '教授', 1)
    admission_catalog_dao.insert_admission_catalog(admission_catalog_info)
    retrieved_info = admission_catalog_dao.get_admission_catalog(5)
    if retrieved_info:
        print(f"Retrieved: {retrieved_info.preliminary_subject}")
    admission_catalog_dao.delete_admission_catalog(5)

    # 关闭数据库连接
    db_connection.close()

if __name__ == "__main__":
    test_database_operations()